﻿Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports System.IO
Imports System.Data.OleDb
Imports System.Math



Public Class Boni

    Public PDFNumber As Integer = 1

    Private Sub Boni_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        My.Settings.BoniPos = Me.Location
        My.Settings.BoniSize = Me.Size
    End Sub

    Private Sub Boni_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'CADISDataSetBoni.V1014' table. You can move, or remove it, as needed.
        Me.V1014TableAdapter.Fill(Me.CADISDataSetBoni.V1014)
        'TODO: This line of code loads data into the 'CADISDataSetBoni.V1004' table. You can move, or remove it, as needed.
        Me.V1004TableAdapter.Fill(Me.CADISDataSetBoni.V1004)
        'TODO: This line of code loads data into the 'CADISDataSetBoni.F0013' table. You can move, or remove it, as needed.
        Me.F0013TableAdapter.Fill(Me.CADISDataSetBoni.F0013)

        'TODO: This line of code loads data into the 'CADISDataSetBoni.F0012' table. You can move, or remove it, as needed.
        Me.F0012TableAdapter.Fill(Me.CADISDataSetBoni.F0012)

        Me.GridView1.BestFitColumns()
        Me.GridView2.BestFitColumns()

        Me.Location = My.Settings.BoniPos
        Me.Size = My.Settings.BoniSize

        Me.SplitContainerControl1.SplitterPosition = My.Settings.Bsplit1
        Me.SplitContainerControl2.SplitterPosition = My.Settings.Bsplit2
        Me.SplitContainerControl3.SplitterPosition = My.Settings.Bsplit3

    End Sub

    Function GetDataConfig(ByVal selectCommand As String)
        GetDataConfig = ""

        Try
            Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
            Dim cmd As New System.Data.SqlClient.SqlCommand
            Dim reader As SqlClient.SqlDataReader = Nothing

            cmd.CommandText = selectCommand
            cmd.Connection = sqlConnection1

            sqlConnection1.Open()

            reader = cmd.ExecuteReader
            While reader.Read
                GetDataConfig = Trim(reader(1).ToString) & "/" & _
                                Trim(reader(2).ToString) & "/" & _
                                Trim(reader(3).ToString) & "/" & _
                                Trim(reader(4).ToString) & "/" & _
                                Trim(reader(5).ToString) & "/" & _
                                Trim(reader(6).ToString) & "/" & _
                                Trim(reader(7).ToString) & "/" & _
                                Trim(reader(8).ToString) & "/" & _
                                Trim(reader(9).ToString) & "/" & _
                                Trim(reader(10).ToString) & "/" & _
                                Trim(reader(11).ToString) & "/" & _
                                Trim(reader(12).ToString) & "/" & _
                                Trim(reader(13).ToString) & "/" & _
                                Trim(reader(14).ToString) & "/" & _
                                Trim(reader(15).ToString)
            End While
            sqlConnection1.Close()

        Catch ex As OleDbException

        End Try


    End Function

    Function GetTotalYear(ByVal selectCommand As String)
        GetTotalYear = ""

        Try
            Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
            Dim cmd As New System.Data.SqlClient.SqlCommand
            Dim reader As SqlClient.SqlDataReader = Nothing

            cmd.CommandText = selectCommand
            cmd.Connection = sqlConnection1

            sqlConnection1.Open()

            reader = cmd.ExecuteReader
            While reader.Read
                GetTotalYear = Trim(reader(0).ToString) & "/" & _
                                Trim(reader(1).ToString) & "/" & _
                                Trim(reader(2).ToString) & "/" & _
                                Trim(reader(3).ToString) & "/" & _
                                Trim(reader(4).ToString) & "/" & _
                                Trim(reader(5).ToString) & "/" & _
                                Trim(reader(6).ToString) & "/" & _
                                Trim(reader(7).ToString) & "/" & _
                                Trim(reader(8).ToString) & "/" & _
                                Trim(reader(9).ToString) & "/" & _
                                Trim(reader(10).ToString) & "/" & _
                                Trim(reader(11).ToString) & "/" & _
                                Trim(reader(12).ToString) & "/" & _
                                Trim(reader(13).ToString) & "/"
            End While
            sqlConnection1.Close()

        Catch ex As OleDbException

        End Try


    End Function


    Function GetExtraBoni(ByVal selectCommand As String)
        GetExtraBoni = ""

        Try
            Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
            Dim cmd As New System.Data.SqlClient.SqlCommand
            Dim reader As SqlClient.SqlDataReader = Nothing

            cmd.CommandText = selectCommand
            cmd.Connection = sqlConnection1

            sqlConnection1.Open()

            reader = cmd.ExecuteReader
            While reader.Read
                GetExtraBoni = Trim(reader(0).ToString) & "/" & _
                               Trim(reader(1).ToString)
            End While
            sqlConnection1.Close()

        Catch ex As OleDbException

        End Try


    End Function

    Function GetDataTechVal(ByVal selectCommand As String)
        GetDataTechVal = ""

        Try
            Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
            Dim cmd As New System.Data.SqlClient.SqlCommand
            Dim reader As SqlClient.SqlDataReader = Nothing

            cmd.CommandText = selectCommand
            cmd.Connection = sqlConnection1

            sqlConnection1.Open()

            reader = cmd.ExecuteReader
            While reader.Read
                GetDataTechVal = Trim(reader(0).ToString)
            End While
            sqlConnection1.Close()

        Catch ex As OleDbException

        End Try


    End Function

    Private Sub BarButtonItem1_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem1.ItemClick

        Dim Budget As Decimal = 0
        Dim Boni As Decimal = 0
        Dim TBoni As Decimal = 0
        Dim s As String = ""
        Dim x() As String
        Dim y() As String

        Dim sum As Decimal
        Dim max As Decimal
        Dim min As Decimal

        Dim vBudget As Decimal
        Dim vPrämie As Decimal
        Dim vSumme As Decimal
        Dim vMBudget As Decimal
        Dim vEBudget As Decimal
        Dim vErfüllt As Decimal
        Dim vSBudget As Decimal
        Dim vMPrämie As Decimal
        Dim vRPrämie As Decimal
        Dim vPPrämie As Decimal

        Dim PDFPage As Boolean = False

        If Me.ComboBox1.SelectedItem <> Nothing And Me.ComboBox2.SelectedItem <> Nothing Then



            'Create Document class obejct and set its size to letter and give space left, right, Top, Bottom Margin
            Dim doc As New Document(PageSize.A4, 0, 0, 0, 0)
            Dim writer As PdfWriter = PdfWriter.GetInstance(doc, New FileStream("c:\" & PDFNumber & ".pdf", FileMode.Create))

            'Open Document to write
            doc.Open()
            Dim cb As PdfContentByte = writer.DirectContent
            Dim vt As New VerticalText(cb)
            ' Dim template As PdfTemplate = cb.CreateTemplate(50, 50)

            'Get image from resources
            Dim draw As System.Drawing.Image = System.Drawing.Image.FromHbitmap(My.Resources.logo.GetHbitmap)
            Dim img As iTextSharp.text.Image = iTextSharp.text.Image.GetInstance(draw, System.Drawing.Imaging.ImageFormat.Png)

            Dim drawer As System.Drawing.Image = System.Drawing.Image.FromHbitmap(My.Resources.Error_72.GetHbitmap)
            Dim imger As iTextSharp.text.Image = iTextSharp.text.Image.GetInstance(drawer, System.Drawing.Imaging.ImageFormat.Png)



            'Resize image depend upon your need
            img.ScaleToFit(140.0F, 140.0F)

            'Give some space after the image
            img.SpacingAfter = 1.0F
            img.Alignment = Image.TEXTWRAP Or Image.ALIGN_RIGHT

            img.IndentationLeft = 9.0F
            img.BorderWidth = 1.0F

            sum = Val(GetDataTechVal("select sum(gtotal) from v1004 where jlyear = '" & Trim(Me.ComboBox2.SelectedItem.ToString) & "' and JLMOYR = '" & Trim(Me.ComboBox1.SelectedItem.ToString) & "'"))
            max = Val(GetDataTechVal("select top 1 gtotal from v1004 where jlyear = '" & Trim(Me.ComboBox2.SelectedItem.ToString) & "' and JLMOYR = '" & Trim(Me.ComboBox1.SelectedItem.ToString) & "' order by gtotal desc"))
            min = Val(GetDataTechVal("select top 1 gtotal from v1004 where jlyear = '" & Trim(Me.ComboBox2.SelectedItem.ToString) & "' and JLMOYR = '" & Trim(Me.ComboBox1.SelectedItem.ToString) & "' and GTotal > 0 order by  gtotal asc"))

            s = GetDataConfig("SELECT * FROM F0012 WHERE JLYEAR = '" & Trim(Me.ComboBox2.SelectedItem.ToString) & "'")
            x = Split(s, "/")

            'Get Tech list
            Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
            Dim cmd As New System.Data.SqlClient.SqlCommand
            Dim reader As SqlClient.SqlDataReader = Nothing

            cmd.CommandText = "SELECT JLEMNM, GTOTAL FROM v1004 WHERE JLYEAR = '" & Trim(Me.ComboBox2.SelectedItem.ToString) & "' " & _
                              "AND JLMOYR = '" & Trim(Me.ComboBox1.SelectedItem.ToString) & "'"
            cmd.Connection = sqlConnection1

            sqlConnection1.Open()

            reader = cmd.ExecuteReader
            While reader.Read

                PDFPage = True
                'PDF page header
                PDFPlaceHeaderLogo(cb, doc, img)
                ' PDFPlaceHeader(cb, 20, 790, "Prämien für Periode: " & Trim(Me.ComboBox1.SelectedItem.ToString) & "/" & Trim(Me.ComboBox2.SelectedItem.ToString), 12)

                PDFPlaceSingleLine(cb, 20, 710, "Prämien für Periode", 12)
                PDFPlaceSingleLine(cb, 250, 710, Trim(Me.ComboBox1.SelectedItem.ToString) & "/" & Trim(Me.ComboBox2.SelectedItem.ToString), 12)
                PDFPlaceSingleLine(cb, 400, 710, "Prime pour la period", 12)

                'Tech name
                PDFPlaceSingleLine(cb, 20, 650, Trim(reader(0).ToString), 18)


                'Grund daten
                PDFPlaceSingleLine(cb, 20, 610, "Grunddaten", 12)
                PDFPlaceSingleLine(cb, 400, 610, "données de base", 12)
                PDFPlaceBar(cb, 20, 547, 600)

                'Get etra boni
                y = Split(GetExtraBoni("SELECT JLPRICE, JLDESC FROM F0013 WHERE JLTECH = '" & Trim(reader(0).ToString) & "' AND JLYEAR = '" & Trim(Me.ComboBox2.SelectedItem.ToString) & "' AND JLMONTH = '" & Trim(Me.ComboBox1.SelectedItem.ToString) & "' "), "/")

                'Budget
                vBudget = Val(x(1).ToString)
                PDFPlaceSingleLine(cb, 20, 580, "Budget Service " & Trim(x(0).ToString) & ":", 12)
                PDFPlaceSingleLine(cb, 250, 580, Format(vBudget, "##,##0"), 12)
                PDFPlaceSingleLine(cb, 400, 580, ":Budget du service " & Trim(x(0).ToString), 12)

                'Prämie
                vPrämie = Val(x(2).ToString) * 100
                PDFPlaceSingleLine(cb, 20, 560, "Prämienanteil : ", 12)
                PDFPlaceSingleLine(cb, 250, 560, Round(vPrämie, 2) & " %", 12)
                PDFPlaceSingleLine(cb, 400, 560, ":part de la prime", 12)

                'Summe
                vSumme = Val(x(2).ToString) * Val(x(1).ToString)
                PDFPlaceSingleLine(cb, 20, 540, "Prämie Total pro Jahr :", 12)
                PDFPlaceSingleLine(cb, 250, 540, Format(vSumme, "##,##0"), 12)
                PDFPlaceSingleLine(cb, 400, 540, ":prime total par année", 12)

                PDFPlaceBar(cb, 20, 547, 530)

                'Monatergebnis
                PDFPlaceSingleLine(cb, 20, 490, "Monatsergebnis", 12)
                PDFPlaceSingleLine(cb, 400, 490, "résultat du mois", 12)
                PDFPlaceBar(cb, 20, 547, 480)

                'Monat Budget
                vMBudget = Val(x(1).ToString) / 12
                PDFPlaceSingleLine(cb, 20, 460, "Monatsbudget :", 12)
                PDFPlaceSingleLine(cb, 250, 460, Format(vMBudget, "##,##0"), 12)
                PDFPlaceSingleLine(cb, 400, 460, ":budget mensuel", 12)

                'Erreicht
                vEBudget = Val(x(Val(Me.ComboBox1.SelectedItem.ToString) + 2).ToString)
                PDFPlaceSingleLine(cb, 20, 440, "Budget erreicht :", 12)
                PDFPlaceSingleLine(cb, 250, 440, Format(vEBudget, "##,##0"), 12)
                PDFPlaceSingleLine(cb, 400, 440, ":budget atteint", 12)

                'Erfüllt
                vErfüllt = Val(x(Val(Me.ComboBox1.SelectedItem.ToString) + 2).ToString) / 1000 / (Val(x(1).ToString) / 1000 / 12) * 100
                PDFPlaceSingleLine(cb, 20, 420, "Erfüllungsgrad :", 12)
                PDFPlaceSingleLine(cb, 250, 420, Round(vErfüllt, 2) & " %", 12)
                PDFPlaceSingleLine(cb, 400, 420, ":degré de réalisation", 12)

                'Monat
                vMPrämie = Val(x(2).ToString) * Val(x(1).ToString) / 12
                PDFPlaceSingleLine(cb, 20, 400, "Prämie gerechnet (Budget) :", 12)
                PDFPlaceSingleLine(cb, 250, 400, Format(vMPrämie, "##,##0"), 12)
                PDFPlaceSingleLine(cb, 400, 400, ":prime calculée (budget)", 12)

                'Reell auszahlen
                vRPrämie = Val(x(2).ToString) * Val(x(1).ToString) / 12 * vErfüllt / 100
                PDFPlaceSingleLine(cb, 20, 380, "Prämie effektiv (Budget) :", 12)
                PDFPlaceSingleLine(cb, 250, 380, Format(vRPrämie, "##,##0"), 12)
                PDFPlaceSingleLine(cb, 400, 380, ":prime effective (budget)", 12)

                'total Umsatzt
                vSBudget = Val(GetDataTechVal("select gtotal from v1004 where jlyear = '" & Trim(Me.ComboBox2.SelectedItem.ToString) & "' and JLMOYR = '" & Trim(Me.ComboBox1.SelectedItem.ToString) & "' and jlemnm = '" & Trim(reader(0).ToString) & "'"))
                PDFPlaceSingleLine(cb, 20, 360, "Umsatz Techniker :", 12)
                PDFPlaceSingleLine(cb, 250, 360, Format(sum, "##,##0"), 12)
                PDFPlaceSingleLine(cb, 400, 360, ":chiffre d'affaire SAV", 12)

                'Selbst Erreicht
                vSBudget = Val(GetDataTechVal("select gtotal from v1004 where jlyear = '" & Trim(Me.ComboBox2.SelectedItem.ToString) & "' and JLMOYR = '" & Trim(Me.ComboBox1.SelectedItem.ToString) & "' and jlemnm = '" & Trim(reader(0).ToString) & "'"))
                PDFPlaceSingleLine(cb, 20, 340, "Eigenen Umsatz :", 12)
                PDFPlaceSingleLine(cb, 250, 340, Format(vSBudget, "##,##0"), 12)
                PDFPlaceSingleLine(cb, 400, 340, ":propre chiffre d'affaire", 12)

                PDFPlaceBar(cb, 20, 547, 330)

                'Monatergebnis
                PDFPlaceSingleLine(cb, 20, 290, "Prämienergebnis", 12)
                PDFPlaceSingleLine(cb, 400, 290, "Prime mensuelle", 12)
                PDFPlaceBar(cb, 20, 547, 280)

                'max Prämie
                vPPrämie = vRPrämie / sum * max
                PDFPlaceSingleLine(cb, 20, 260, "Maximale Prämie :", 12)
                PDFPlaceSingleLine(cb, 250, 260, Format(vPPrämie, "##,##0"), 12)
                PDFPlaceSingleLine(cb, 400, 260, ":prime maximale", 12)

                'min Prämie
                vPPrämie = vRPrämie / sum * min
                PDFPlaceSingleLine(cb, 20, 240, "Minimale Prämie :", 12)
                PDFPlaceSingleLine(cb, 250, 240, Format(vPPrämie, "##,##0"), 12)
                PDFPlaceSingleLine(cb, 400, 240, ":prime minimale", 12)

                'Prämie
                vPPrämie = vRPrämie / sum * vSBudget
                PDFPlaceSingleLineBold(cb, 20, 220, "Ihre Prämie :", 12)
                PDFPlaceSingleLineBold(cb, 250, 220, Format(vPPrämie, "##,##0"), 12)
                PDFPlaceSingleLineBold(cb, 400, 220, ":votre prime", 12)

                PDFPlaceBar(cb, 20, 547, 210)

                If y(0) <> "" Then
                    'extra Prämie
                    PDFPlaceSingleLine(cb, 20, 170, y(1), 12)
                    PDFPlaceBar(cb, 20, 547, 160)

                    PDFPlaceSingleLine(cb, 20, 140, "Extra Prämie :", 12)
                    PDFPlaceSingleLine(cb, 250, 140, Format(Val(y(0)), "##,##0"), 12)
                    PDFPlaceSingleLine(cb, 400, 140, ":prime supplèmentaire", 12)

                    PDFPlaceBar(cb, 20, 547, 130)

                    PDFPlaceSingleLineBold(cb, 20, 90, "Total Prämie :", 12)
                    PDFPlaceSingleLineBold(cb, 250, 90, Format(Val(y(0)) + vPPrämie, "##,##0"), 12)
                    PDFPlaceSingleLineBold(cb, 400, 90, ":total des primes", 12)

                End If

                PDFPlaceVerticalLine(cb, 10, 50, "JLD004 / V 1.001  " & Format(Date.Now, "dd/MM/yyy HH:mm"), 4)
                PDFPlaceFooter(cb)

                'cb.AddTemplate(template, 200, 200)

                doc.NewPage()

            End While

            sqlConnection1.Close()

            If Not PDFPage Then
                'No data 

                doc.Add(imger)
                cb.BeginText()
                cb.SetTextMatrix(20, 700)
                cb.SetFontAndSize(BaseFont.CreateFont(BaseFont.HELVETICA, BaseFont.CP1252, False), 18)
                cb.ShowText("No turn over for this periode....")
                cb.EndText()

            End If


            'Fill template
            'template.BeginText()
            'template.SetFontAndSize(BaseFont.CreateFont(BaseFont.HELVETICA, BaseFont.CP1252, False), 8)
            'template.SetTextMatrix(0, 0)
            'template.ShowText("" & Convert.ToString((writer.PageNumber - 1)))
            'template.EndText()

            'Doc reference

            doc.Close()
            Me.WebBrowser1.Navigate("c:\" & PDFNumber & ".pdf")
            PDFNumber = PDFNumber + 1

        End If

    End Sub


    Function PDFPlaceLine(ByRef cb As Object, ByVal x As Integer, ByVal y As Integer, ByVal Text As String, ByVal Value As String)
        cb.BeginText()
        cb.SetTextMatrix(x, y)
        cb.SetFontAndSize(BaseFont.CreateFont(BaseFont.HELVETICA, BaseFont.CP1252, False), 12)
        cb.ShowText(Text)
        cb.SetTextMatrix(x + 100, y)
        cb.ShowText(":")
        cb.ShowTextAligned(PdfContentByte.ALIGN_RIGHT, Value, x + 240, y, 0)
        cb.EndText()
        PDFPlaceLine = True
    End Function

    Function PDFPlaceSingleLine(ByRef cb As Object, ByVal x As Integer, ByVal y As Integer, ByVal Text As String, ByVal Size As Integer)
        cb.BeginText()
        cb.SetTextMatrix(x, y)
        cb.SetFontAndSize(BaseFont.CreateFont(BaseFont.HELVETICA, BaseFont.CP1252, False), Size)
        cb.ShowText(Text)
        cb.EndText()
        PDFPlaceSingleLine = True
    End Function

    Function PDFPlaceSingleLineBold(ByRef cb As Object, ByVal x As Integer, ByVal y As Integer, ByVal Text As String, ByVal Size As Integer)
        cb.BeginText()
        cb.SetTextMatrix(x, y)
        cb.SetFontAndSize(BaseFont.CreateFont(BaseFont.HELVETICA_BOLD, BaseFont.CP1252, False), Size)
        cb.ShowText(Text)
        cb.EndText()
        PDFPlaceSingleLineBold = True
    End Function

    Function PDFPlaceHeader(ByRef cb As Object, ByVal x As Integer, ByVal y As Integer, ByVal Text As String, ByVal Size As Integer)
        cb.BeginText()
        cb.SetTextMatrix(x, y)
        cb.SetFontAndSize(BaseFont.CreateFont(BaseFont.HELVETICA, BaseFont.CP1252, False), Size)
        cb.ShowText(Text)
        cb.EndText()
        PDFPlaceHeader = True
    End Function


    Function PDFPlaceVerticalLine(ByRef cb As Object, ByVal x As Integer, ByVal y As Integer, ByVal Text As String, ByVal Size As Integer)
        cb.BeginText()
        cb.SetTextMatrix(x, y)
        cb.SetFontAndSize(BaseFont.CreateFont(BaseFont.HELVETICA, BaseFont.CP1252, False), Size)
        cb.ShowTextAligned(PdfContentByte.ALIGN_RIGHT, Text, x, y, 270)
        'cb.ShowText(Text)
        cb.EndText()
        PDFPlaceVerticalLine = True
    End Function

    Function PDFPlaceHeaderLogo(ByRef cb As Object, ByRef doc As Object, ByRef img As Object)
        doc.Add(img)
        cb.SetLineWidth(0.5)
        cb.MoveTo(20, 700)
        cb.LineTo(567, 700)
        cb.Stroke()
        PDFPlaceHeaderLogo = True
    End Function

    Function PDFPlaceBar(ByRef cb As Object, ByVal x As Integer, ByVal length As Integer, ByVal y As Integer)

        cb.SetLineWidth(0.5)
        cb.MoveTo(x, y)
        cb.LineTo(x + length, y)
        cb.Stroke()
        PDFPlaceBar = True
    End Function

    Function PDFPlaceFooter(ByRef cb As Object)
        cb.SetLineWidth(0.5)
        cb.MoveTo(20, 50)
        cb.LineTo(567, 50)
        cb.Stroke()
        cb.BeginText()
        cb.SetTextMatrix(20, 30)
        cb.SetFontAndSize(BaseFont.CreateFont(BaseFont.HELVETICA, BaseFont.CP1252, False), 8)
        cb.ShowText("record türautomation ag  Allmendstrasse 24  8320 Fehraltorf")
        cb.EndText()
        PDFPlaceFooter = True
    End Function



    Private Sub BarButtonItem2_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem2.ItemClick
        Me.F0013TableAdapter.Update(Me.CADISDataSetBoni.F0013)
        Me.F0012TableAdapter.Update(Me.CADISDataSetBoni.F0012)
    End Sub

    Private Sub SplitContainerControl1_SplitterPositionChanged(sender As Object, e As System.EventArgs) Handles SplitContainerControl1.SplitterPositionChanged
        My.Settings.Bsplit1 = Me.SplitContainerControl1.SplitterPosition
    End Sub

    Private Sub SplitContainerControl2_SplitterPositionChanged(sender As Object, e As System.EventArgs) Handles SplitContainerControl2.SplitterPositionChanged
        My.Settings.Bsplit2 = Me.SplitContainerControl2.SplitterPosition
    End Sub

    Private Sub SplitContainerControl3_SplitterPositionChanged(sender As Object, e As System.EventArgs) Handles SplitContainerControl3.SplitterPositionChanged
        My.Settings.Bsplit3 = Me.SplitContainerControl3.SplitterPosition
    End Sub

    Private Sub BarButtonItem3_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem3.ItemClick

        Dim Budget As Decimal = 0
        Dim Boni As Decimal = 0
        Dim TBoni As Decimal = 0
        Dim s As String = ""
        Dim x() As String
        Dim praemien(11) As Decimal
        Dim turnower(11) As Decimal
        Dim totalpraemien(12) As Decimal
        Dim i As Integer
        Dim vSBudget As Decimal
        Dim sum As Decimal

        Dim PDFPage As Boolean = False

        If Me.ComboBox2.SelectedItem <> Nothing Then

            'get real boni per month
            s = GetDataConfig("SELECT * FROM F0012 WHERE JLYEAR = '" & Trim(Me.ComboBox2.SelectedItem.ToString) & "'")
            x = Split(s, "/")

            For i = 0 To 11
                praemien(i) = Val(x(i + 3)) / Val(x(1) / 12) * Val(x(1)) * Val(x(2)) / 12
            Next

            'get total turn over per month.
            For i = 0 To 11
                turnower(i) = Val(GetDataTechVal("select sum(gtotal) from v1004 where jlyear = '" & Trim(Me.ComboBox2.SelectedItem.ToString) & "' and JLMOYR = '" & i + 1 & "'"))
            Next

            'Create Document class obejct and set its size to letter and give space left, right, Top, Bottom Margin
            Dim doc As New Document(PageSize.A4.Rotate, 0, 0, 10, 10)
            Dim writer As PdfWriter = PdfWriter.GetInstance(doc, New FileStream("c:\" & PDFNumber & ".pdf", FileMode.Create))

            'Open Document to write
            doc.Open()
            Dim font8 As Font = FontFactory.GetFont("ARIAL", 5)
            Dim cb As PdfContentByte = writer.DirectContent
            Dim vt As New VerticalText(cb)

            Dim paragraphTable1 As Paragraph = New Paragraph
            paragraphTable1.SpacingBefore = 100

            'Get image from resources
            Dim draw As System.Drawing.Image = System.Drawing.Image.FromHbitmap(My.Resources.logo.GetHbitmap)
            Dim img As iTextSharp.text.Image = iTextSharp.text.Image.GetInstance(draw, System.Drawing.Imaging.ImageFormat.Png)

            'Resize image depend upon your need
            img.ScaleToFit(140.0F, 140.0F)

            'Give some space after the image
            img.SpacingAfter = 1.0F
            img.Alignment = Image.TEXTWRAP Or Image.ALIGN_RIGHT

            img.IndentationLeft = 9.0F
            img.BorderWidth = 1.0F

            'Get Tech list
            Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
            Dim cmd As New System.Data.SqlClient.SqlCommand
            Dim reader As SqlClient.SqlDataReader = Nothing

            cmd.CommandText = "SELECT " & _
                  "CASE WHEN GROUPING(jlemnm) = 1 THEN 'Summe' ELSE jlemnm END as 'Techniker', " & _
                  "SUM(CASE [jlmoyr] WHEN '1' Then gtotal ELSE 0 END) As '1', " & _
                  "SUM(CASE [jlmoyr] WHEN '2' Then gtotal ELSE 0 END) As '2', " & _
                  "SUM(CASE [jlmoyr] WHEN '3' Then gtotal ELSE 0 END) As '3', " & _
                  "SUM(CASE [jlmoyr] WHEN '4' Then gtotal ELSE 0 END) As '4', " & _
                  "SUM(CASE [jlmoyr] WHEN '5' Then gtotal ELSE 0 END) As '5', " & _
                  "SUM(CASE [jlmoyr] WHEN '6' Then gtotal ELSE 0 END) As '6', " & _
                  "SUM(CASE [jlmoyr] WHEN '7' Then gtotal ELSE 0 END) As '7', " & _
                  "SUM(CASE [jlmoyr] WHEN '8' Then gtotal ELSE 0 END) As '8', " & _
                  "SUM(CASE [jlmoyr] WHEN '9' Then gtotal ELSE 0 END) As '9', " & _
                  "SUM(CASE [jlmoyr] WHEN '10' Then gtotal ELSE 0 END) As '10', " & _
                  "SUM(CASE [jlmoyr] WHEN '11' Then gtotal ELSE 0 END) As '11', " & _
                  "SUM(CASE [jlmoyr] WHEN '12' Then gtotal ELSE 0 END) As '12', " & _
                  "SUM(gtotal) As Summe " & _
                  "FROM[V1004] " & _
                  "WHERE jlyear = '" & Trim(Me.ComboBox2.SelectedItem.ToString) & "' " & _
                  "GROUP BY jlemnm " & _
                  "WITH CUBE"
            cmd.Connection = sqlConnection1

            sqlConnection1.Open()

            reader = cmd.ExecuteReader

            'Start PDF
            PDFPlaceHeaderLogo(cb, doc, img)
            PDFPlaceSingleLine(cb, 20, 550, "Prämienansicht", 14)
            PDFPlaceSingleLine(cb, 20, 520, "Periode " & Trim(Me.ComboBox2.SelectedItem.ToString), 10)

            Dim table As New PdfPTable(27)
            Dim widths = New Single() {2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1}
            table.SetWidths(widths)
            table.TotalWidth = 800
            table.LockedWidth = True
            table.DefaultCell.BorderWidth = 0.1

            'Table header
            table.AddCell(New PdfPCell(New Phrase(New Chunk("Techniker", font8))))
            For x1 = 0 To 11
                table.AddCell(New PdfPCell(New Phrase(New Chunk(x1 + 1, font8))))
                table.AddCell(New PdfPCell(New Phrase(New Chunk("Prämie", font8))))
            Next
            table.AddCell(New PdfPCell(New Phrase(New Chunk("Total Um.", font8))))
            table.AddCell(New PdfPCell(New Phrase(New Chunk("Total Pr.", font8))))
            'table.SpacingBefore = 100

            While reader.Read

                sum = 0

                For x1 = 0 To 13
                    If x1 = 0 Then
                        table.AddCell(New PdfPCell(New Phrase(New Chunk(reader(x1).ToString, font8))))
                    Else
                        Dim cell As New PdfPCell(New Phrase(New Chunk(Format(Val(reader(x1).ToString), "##,##0"), font8)))
                        cell.HorizontalAlignment = 2
                        If reader(0).ToString = "Summe" Then
                            cell.BackgroundColor = New iTextSharp.text.BaseColor(220, 220, 255)
                        Else
                            cell.BackgroundColor = New iTextSharp.text.BaseColor(255, 255, 255)
                        End If

                        If x1 = 13 Then
                            cell.BackgroundColor = New iTextSharp.text.BaseColor(220, 220, 255)
                        End If

                        table.AddCell(cell)
                    End If

                    If x1 > 0 And x1 < 13 Then
                        vSBudget = Val(GetDataTechVal("select gtotal from v1004 where jlyear = '" & Trim(Me.ComboBox2.SelectedItem.ToString) & "' and JLMOYR = '" & x1 & "' and jlemnm = '" & Trim(reader(0).ToString) & "'"))
                        If turnower(x1 - 1) = 0 Then
                            i = 0
                        Else
                            i = vSBudget / turnower(x1 - 1) * praemien(x1 - 1)
                        End If

                        sum = sum + i
                        totalpraemien(x1 - 1) = totalpraemien(x1 - 1) + i
                        If reader(0).ToString = "Summe" Then
                            i = totalpraemien(x1 - 1)
                        End If
                        Dim cell As New PdfPCell(New Phrase(New Chunk(Format(Val(i.ToString), "##,##0"), font8)))
                        cell.HorizontalAlignment = 2

                        If reader(0).ToString = "Summe" Then
                            cell.BackgroundColor = New iTextSharp.text.BaseColor(150, 255, 150)
                        Else
                            cell.BackgroundColor = New iTextSharp.text.BaseColor(220, 255, 220)
                        End If

                        table.AddCell(cell)
                    End If
                Next


                totalpraemien(12) = totalpraemien(12) + sum
                If reader(0).ToString = "Summe" Then
                    sum = totalpraemien(12)
                End If
                Dim cell1 As New PdfPCell(New Phrase(New Chunk(Format(Val(sum), "##,##0"), font8)))
                cell1.HorizontalAlignment = 2
                cell1.BackgroundColor = New iTextSharp.text.BaseColor(150, 255, 150)
                table.AddCell(cell1)

            End While

            paragraphTable1.Add(table)
            doc.Add(paragraphTable1)
            sqlConnection1.Close()

            'Doc reference
            PDFPlaceVerticalLine(cb, 10, 50, "JLD004 / V 1.003  " & Format(Date.Now, "dd/MM/yyy HH:mm"), 4)

            doc.Close()
            Me.WebBrowser1.Navigate("c:\" & PDFNumber & ".pdf")
            PDFNumber = PDFNumber + 1

        End If


    End Sub



End Class